#!/usr/bin/python
# -*- coding: UTF-8 -*-
from Config import *
import pymysql

db=pymysql.connect(host=MYSQL_URL,db=MYSQL_TABLE,user=MYSQL_USER,passwd=MYSQL_PAW,charset="utf8")
cursor=db.cursor()

sql_1="select ascii('A') from dual"
sql_2="select DATE_FORMAT('2014-07-01 22:15','%Y%m%d %H%i') from dual"
sql_3="select TRUNCATE(1.01,1) from dual"
sql_4="select deptno, max(sal) from emp group by deptno"
sql_5="select max(hiredate) from emp"
#cast数据类型
'''
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
'''
#类型转换函数
sql_6="select cast('1234' as signed) from dual"
sql_7="select cast('1.2345' as DECIMAL) from dual"

#日期函数
sql_8=" select DATE_FORMAT('20140701','%Y%m%d') from dual"
#日期差值
sql_9="SELECT DATEDIFF('2015-06-12','2015-06-29') AS DiffDate"
sql_10="SELECT TIMESTAMPDIFF(MONTH,'2009-12-01','2009-09-01'); "
sql_11="SELECT TIMESTAMPDIFF(YEAR,'2009-12-01','2018-09-01'); "

#字符类函数
#字符串连接
sql_12="select concat(concat('last','winner'),'') from dual"
#截取,默认空格,只能一个字符
sql_13="select trim('a' from 'aabdwecab') from dual;"
#取字字符串


#聚合函数
#聚合前，用where过滤
#聚合后，用having过滤
#按部门和职位分组，哪些部门的哪些职位拥有的雇员数大于1（雇员薪水需大于1000），列出这些部门 和职位，并列出相应的雇员数
sql_14="select deptno,job,count(*) from emp WHERE sal >1000 GROUP by deptno,job HAVING count(*)>1"

#分析函数总是在除了ORDER BY之外的其他子句运算后才执行的，所以它不能出现在
#where、 group by等子句中，只能出现在select列表和order by子句中

#统计函数
#求出每个部门按月的累计销售额
sql_15="select job, sum(sal) from emp group by job"

#查询没一个部门雇佣时间最大和最短的时间
sql_16="select deptno, max(hiredate), min(hiredate) from emp group by deptno";
#查询每一个销售等级销售总额
sql_17="select grade, sum(sal) from emp, salgrade where sal between losal and hisal group by grade"
#
sql_18="select deptno, count(*) from emp right join dept using(deptno) group by deptno"

sql_19="select e.deptno, grade, min(sal), max(sal) from emp e, salgrade s, dept d where sal between losal and hisal and e.deptno=d.deptno group by e.deptno, grade order by 1,2"

sql_20="select e.deptno, grade, count(*) from emp e, salgrade s where sal between losal and hisal group by e.deptno, grade order by deptno, grade;"


cursor.execute(sql_20)
db.commit()

resutl=cursor.fetchall()
print(resutl)